package edu.drexel.cs575.taskManager.services.group;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


public class group_api {
	
	public Connection connect(){
		
		 Connection con = null; 
		try {  
		      Class.forName("com.mysql.jdbc.Driver").newInstance();  
		       
		      con = DriverManager.getConnection("jdbc:mysql://localhost:3306/groups",  
		        "root", "notasecret");  //   error is here **  error is here ***  error is here   
		  
		     	      
		      } catch(SQLException e) {  
		        
		        e.printStackTrace();  
		    }  
		    catch(Exception e) {  
		      System.err.println("Exception: " + e.getMessage());  
		    }   
		     
		return con;  
	}

	public void connectclose(Connection con){
	
		try {
		con.close();
		} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		}

	}
	

	public boolean createGroup (String TaskID, String Groupnm, String Appname)
	{
		Statement stmt;
		String Appid = returnAppid(Groupnm);
		
		try {
		if (Appid.equals("")){
			Connection con = connect();
			String insert = "insert into AppGroup (AppName) VALUE ('"+Appname+"')";
			stmt = con.createStatement();
	   		stmt.executeUpdate(insert);
	   		connectclose(con);
	   		Appid = returnAppid(Appname);
			
		}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		try{
		Connection con = connect();
		String insert2 = "insert into Groupsrvc values ("+Appid+",NULL,'"+Groupnm+"','"+TaskID+"')";
		System.out.println(insert2);
		stmt = con.createStatement();
   		stmt.executeUpdate(insert2);
   		connectclose(con);
   		return true;
		}
		catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		return false;
		}
	}
	
	
	public boolean removeGroup (String Groupnm, String Appname)
	{
		Statement stmt;
		String Appid = returnAppid(Appname);
		
		try{
			Connection con = connect();
			String insert2 = "DELETE from Groupsrvc where Appid_fk="+Appid+" AND GroupNm='"+Groupnm+"'";
			stmt = con.createStatement();
	   		stmt.executeUpdate(insert2);
	   		connectclose(con);
	   		return true;
			}
		catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return false;
			}
	}

	public boolean adduser2group (String Username, String Groupnm){
		Statement stmt;
		String gid=getgroupid(Groupnm);
		try{
			Connection con = connect();
			String insert2 = "insert into AppEntity values (NULL,'"+gid+"','"+Username+"')";
			System.out.println(insert2);
			stmt = con.createStatement();
	   		stmt.executeUpdate(insert2);
	   		connectclose(con);
	   		return true;
			}
			catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
			}
		

	}
	
	public boolean removeuserGroup (String Username, String Groupnm){
		Statement stmt;
		String gid=getgroupid(Groupnm);
		try{
			Connection con = connect();
			String remove = "DELETE from AppEntity where Groupid_fk="+gid+" AND AppEntityName='"+Username+"'";
			stmt = con.createStatement();
	   		int rslt = stmt.executeUpdate(remove);
	   		connectclose(con);
	   		
	   		if (rslt == 1)
	   		return true;
	   		else
	   		return false;
		}
	   		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
			}
	}
	
	public String getTaskID4Group(String Appname,String groupnm){
		String TaskID="";
		String appid=returnAppid(Appname);
		Connection con = connect();
		String select = "Select AppEntityTaskID from Groupsrvc where Appid_fk ='"+appid+"' AND GroupNm = '"+groupnm+"'";
		try {
			
		Statement stmt = con.createStatement();
		ResultSet rs;
		rs = stmt.executeQuery(select);
				
		
		while (rs.next()) {
			  TaskID = rs.getString("AppEntityTaskID");
			}
		}catch (SQLException e) {
			e.printStackTrace();
		}		
		return TaskID;
	}
	
	
	public List<String> getUsersfromGroup(String Appname,String groupnm){
		String appid=returnAppid(Appname);
		String grpid="";
		List<String> users=new ArrayList<String>();
		
		Connection con = connect();
		String select = "Select Groupid from Groupsrvc where Appid_fk ='"+appid+"' AND GroupNm = '"+groupnm+"'";
		
		
		try {
			
		Statement stmt = con.createStatement();
		ResultSet rs;
		rs = stmt.executeQuery(select);
				
		while (rs.next()) {
			  grpid = rs.getString("Groupid");
			}
		
		String select2 = "Select AppEntityName from AppEntity where Groupid_fk ='"+grpid+"'";
		Statement stmt2 = con.createStatement();
		ResultSet rs2;
		rs2 = stmt2.executeQuery(select2);
		
		while (rs2.next()) {
			  users.add(rs2.getString("AppEntityName"));
			}
		
		
		} catch (SQLException e) {
			e.printStackTrace();
		}		
	
	return users;
	}
	
	
	private String getgroupid (String Groupnm){
		
		Connection con = connect();
		String select = "Select Groupid from Groupsrvc where GroupNm ='"+Groupnm+"'";
		String Appid="";
		try {
			
		Statement stmt = con.createStatement();
		ResultSet rs;
		rs = stmt.executeQuery(select);
				
		while (rs.next()) {
			  Appid = rs.getString("Groupid");
			}
		
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		
		connectclose(con);
		return Appid;
	}
	
	private String returnAppid (String Appname){
		
		Connection con = connect();
		String select = "Select Appid from AppGroup where AppName ='"+Appname+"'";
		String Appid="";
		
		try {
			
		Statement stmt = con.createStatement();
		ResultSet rs;
		rs = stmt.executeQuery(select);
				
		while (rs.next()) {
			  Appid = rs.getString("Appid");
			}
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		connectclose(con);
		return Appid;
	}
}